﻿<?xml version="1.0" encoding="utf-8" ?>
<AdoNetDataAccess connection="$ArticleDBConnection$"  commandTimeout="1000" formatter="bizobjlist of Article">
	<FormatterInfo>
		<code for="vars" on="Article">
		int firstDepth = -1;
    bool resetDepth = false;
    bool stripLeading = true;
    bool setup = false;
		</code>
		<code for="postEach" on="Article">
			<![CDATA[
			try
			{
        if(!setup)
        {
          if(info.get("stripLeading") is bool)
            stripLeading = (bool)info.get("stripLeading");
          else if(info.get("stripLeading") is string)
            stripLeading = (string.Compare((string)info.get("stripLeading"), "False", true) == 0)?false:true;
          resetDepth = info.get("resetDepth") is string && string.Compare((string)info.get("resetDepth"), "true", true) == 0;
          setup = true;
        }
          
				if(CurrentBizObj.get("mediaCacheName").ToString().Length==0)
					CurrentBizObj.put("mediaCacheName",info.get("defaultCacheName"));
        if(stripLeading)
        {
          string title = (string)CurrentBizObj.get("baseTitle");
          int titIndex = title.IndexOf("~!");
          if(titIndex != -1)
            CurrentBizObj.put("baseTitle", title.Substring(titIndex+2));
        }
			}
			catch(Exception e)
			{
			}
			 
			try
			{
				//The problem with the following 3 lines is that when we chunk the articles
				//we dont know what the real start dept is.  We need this to be a parameter into
				//the call so we know what the true start depth really is.
        if(resetDepth)
        {
				  if(firstDepth == -1)
					  firstDepth = (int)CurrentBizObj.get("depth");
				  CurrentBizObj.put("depth", (int)CurrentBizObj.get("depth") - firstDepth);						
        }
			}
			catch(Exception e)
			{
			
			}
			
		]]></code>
		<code for="bizObjType" on="Article"><![CDATA[
			obj = App.bizObjMgr.create(info.get("BizObjType") != null?info.get("BizObjType").ToString():"Article");
		]]></code>
		<code for="bizObjListType" on="Article"><![CDATA[
			m_list = App.bizObjMgr.createList(info.get("BizObjType") != null?info.get("BizObjType").ToString():"Article");
		]]></code>
	</FormatterInfo>
	<Sql>
		SELECT ROW_NUMBER() OVER( 
			ORDER BY nc.keyName DESC
			) AS rownum,
				a.id,a.baseTitle,a.body AS baseBody,a.uniqueTitle,nc.dateCreated AS counts_dateCreated,
				u.id AS counts_author_id,u.userName AS counts_author_userName,
				nc.totalL1 AS counts_totalL1,nc.totalViews AS counts_totalViews,
				nc.lastChildUpdated AS counts_lastChildUpdated,lu.userName AS counts_lastChildUpdatedBy_userName,
				
				t.objectId,t.parentId
				
		INTO
			#tmp_tbl_client	
		FROM            
			Articles AS a 
			LEFT OUTER JOIN	ObjectTreeNodes AS t ON a.id = t.objectId
			LEFT OUTER JOIN ObjectTreeCounts as nc on nc.objectId = a.id 
			 LEFT OUTER JOIN Users AS lu ON lu.id = nc.lastChildUpdatedByUserId 
					LEFT OUTER JOIN	Users AS u ON u.id = nc.authorId 
				
		WHERE
			(t.parentHostId = @parentId AND t.id &lt;&gt; @parentId) AND 
			a.companyId = @companyId AND nc.treeName = 'Forum'  
		ORDER BY nc.keyName DESC;
			
			CREATE UNIQUE CLUSTERED INDEX
				idx_uc_rownum
			ON
				#tmp_tbl_client(rownum)
				
			SELECT rownum, 
			id,baseTitle,baseBody,uniqueTitle,counts_dateCreated,counts_author_id,
			counts_author_userName,counts_totalL1,counts_totalViews,
			counts_lastChildUpdated,counts_lastChildUpdatedBy_userName,
			
			objectId,parentId
			FROM
				#tmp_tbl_client
			WHERE
				rownum BETWEEN (@currentPage-1)*@pageSize+1 AND @currentPage*@pageSize
	</Sql>
	<Param name="parentId" type="UniqueIdentifier" />
	<Param name="companyId" type="UniqueIdentifier" />
	<Param name="pageSize" type="Integer" />
	<Param name="currentPage" type="Integer" />
</AdoNetDataAccess>
